
import xlwings as xl
import pandas as pd
import screenShot
# from PySide6.QtWidgets import QApplication

def executeData(sourceFilePath,dataFilePath,plateForm):
    # logBrowser.append('开始读取数据')
    # QApplication.processEvents()

    sheets = pd.read_excel(sourceFilePath, sheet_name=None,dtype=str)
    sheetNames = list(sheets.keys())

    #plateForm是个列表判断 包含 FB
    if 'FB' in plateForm:
        fbDataSheet = pd.read_excel(dataFilePath, sheet_name='FB',dtype=str)
        fb_full_end_row = fbDataSheet.index[-1] + 2
    if 'TT' in plateForm:
        ttDataSheet = pd.read_excel(dataFilePath, sheet_name='TT',dtype=str)
        tt_full_end_row = ttDataSheet.index[-1] + 2
    if 'KW' in plateForm:
        kwDataSheet = pd.read_excel(dataFilePath, sheet_name='KW',dtype=str)
        kw_full_end_row = kwDataSheet.index[-1] + 2
    if 'GG' in plateForm:
        ggDataSheet = pd.read_excel(dataFilePath, sheet_name='GG',dtype=str)
        gg_full_end_row = ggDataSheet.index[-1] + 2

    # logBrowser.append('读取数据完成')
    # QApplication.processEvents()

    # logBrowser.append('尝试启动Excel')
    # QApplication.processEvents()
    app = xl.App(visible=False,add_book=False)
    data_wb = app.books.open(dataFilePath)
    source_wb = app.books.open(sourceFilePath)
    
    # logBrowser.append('Excel启动成功')
    # QApplication.processEvents()

    #data_wb新增一个tmpSheet
    tmpSheet = data_wb.sheets.add()
    tmpSheet.Name = 'tmp'
    tmpSheet.range('A:A').column_width = 20
    tmpSheet.range('B:B').column_width = 40
    tmpSheet.range('C:C').column_width = 40
    tmpSheet.range('D:D').column_width = 10
    tmpSheet.range('E:E').column_width = 30
    tmpSheet.range('F:F').column_width = 10
    tmpSheet.range('A1').copy_picture()

    for sheetName in sheetNames:
        if('截图' in sheetName):
            continue
        sheet = sheets[sheetName]
        
        source_sheet = source_wb.sheets[sheetName]
        png_sheet = source_wb.sheets[sheetName + '截图']

        png_insert_row = 1
        
        # 删除sheet最后一行
        sheet.drop(sheet.tail(1).index,inplace=True)

        dealCount = sheet.shape[0]
        # logBrowser.append('提成表共'+ str(dealCount) + '条数据')

        # 遍历每行 
        for index, row in sheet.iterrows():
            if 'Face' in row['平台']:
                if 'FB' not in plateForm:
                    continue
                dataSheet = data_wb.sheets['FB']
                # 获取广告账号ID为1且广告名称包含qh的数据
                data = fbDataSheet[(fbDataSheet['ID'] == row['帐户编号']) & (fbDataSheet['优化归属'] == sheetName)]
                if data.empty:
                    continue
                # 获取Spend的合计
                sumSpend = data['花费'].astype(float).sum()
                if sumSpend != 0:
                    # logBrowser.append('数据匹配成功，计算优化归属，提成表待匹配数据---'+ str(dealCount - index - 1) + '条')
                    # logBrowser.append('客户:'+ row['客户'] + ' 平台:'+ row['平台'] + ' 帐户编号:'+ row['帐户编号'] + ' 优化归属计算：' + str(sumSpend))
                    # QApplication.processEvents()
                    maxRow = data.shape[0]
                    if maxRow > 15:
                        data = data.tail(15)
                        maxRow = 15
                    data_row_list = data.index.tolist()
                    source_sheet.range('H' + str(index + 2)).value = sumSpend
                    png_insert_row_str = 'A' + str(png_insert_row)
                    screenShot.dealExcel(row['帐户编号'],sheetName,maxRow,dataSheet,data_row_list,sumSpend,fb_full_end_row,tmpSheet,png_sheet,png_insert_row_str)
                    png_insert_row += maxRow
                    png_insert_row += 3
            if 'Tik' in row['平台']:
                if 'TT' not in plateForm:
                    continue
                dataSheet = data_wb.sheets['TT']
                # 获取广告账号ID为1且广告名称包含qh的数据
                data = ttDataSheet[(ttDataSheet['ID'] == row['帐户编号']) & (ttDataSheet['优化归属'] == sheetName)]
                if data.empty:
                    continue
                # 获取Spend的合计
                sumSpend = data['花费'].astype(float).sum()
                if sumSpend != 0:
                    # logBrowser.append('数据匹配成功，计算优化归属，提成表待匹配数据---'+ str(dealCount - index - 1) + '条')
                    # logBrowser.append('客户:'+ row['客户'] + ' 平台:'+ row['平台'] + ' 帐户编号:'+ row['帐户编号'] + ' 优化归属计算：' + str(sumSpend))
                    # QApplication.processEvents()
                    maxRow = data.shape[0]
                    if maxRow > 15:
                        data = data.tail(15)
                        maxRow = 15
                    data_row_list = data.index.tolist()
                    source_sheet.range('H' + str(index + 2)).value = sumSpend
                    png_insert_row_str = 'A' + str(png_insert_row)
                    screenShot.dealExcel(row['帐户编号'],sheetName,maxRow,dataSheet,data_row_list,sumSpend,tt_full_end_row,tmpSheet,png_sheet,png_insert_row_str)
                    png_insert_row += maxRow
                    png_insert_row += 3
            elif 'kwai' in row['平台'].lower():
                if 'KW' not in plateForm:
                    continue
                dataSheet = data_wb.sheets['KW']
                # 获取广告账号ID为1且广告名称包含qh的数据
                data = kwDataSheet[(kwDataSheet['ID'] == row['帐户编号']) & (kwDataSheet['优化归属'] == sheetName) & (kwDataSheet['客户'] == row['客户'])]
                if data.empty:
                    continue
                # 获取Spend的合计
                sumSpend = data['花费'].astype(float).sum()
                if sumSpend != 0:
                    # logBrowser.append('数据匹配成功，计算优化归属，提成表待匹配数据---'+ str(dealCount - index - 1) + '条')
                    # logBrowser.append('客户:'+ row['客户'] + ' 平台:'+ row['平台'] + ' 帐户编号:'+ row['帐户编号'] + ' 优化归属计算：' + str(sumSpend))
                    # QApplication.processEvents()
                    maxRow = data.shape[0]
                    if maxRow > 15:
                        data = data.tail(15)
                        maxRow = 15
                    data_row_list = data.index.tolist()
                    source_sheet.range('H' + str(index + 2)).value = sumSpend
                    png_insert_row_str = 'A' + str(png_insert_row)
                    screenShot.dealExcel(row['帐户编号'],sheetName,maxRow,dataSheet,data_row_list,sumSpend,kw_full_end_row,tmpSheet,png_sheet,png_insert_row_str)
                    png_insert_row += maxRow
                    png_insert_row += 3
            elif 'Google' in row['平台']:
                if 'GG' not in plateForm:
                    continue
                dataSheet = data_wb.sheets['GG']
                # 获取广告账号ID为1且广告名称包含qh的数据
                data = ggDataSheet[(ggDataSheet['ID'] == row['帐户编号']) & (ggDataSheet['优化归属'] == sheetName)]
                if data.empty:
                    continue
                # 获取Spend的合计
                sumSpend = data['花费'].astype(float).sum()
                if sumSpend != 0:
                    # logBrowser.append('数据匹配成功，计算优化归属，提成表待匹配数据---'+ str(dealCount - index - 1) + '条')
                    # logBrowser.append('客户:'+ row['客户'] + ' 平台:'+ row['平台'] + ' 帐户编号:'+ row['帐户编号'] + ' 优化归属计算：' + str(sumSpend))
                    # QApplication.processEvents()
                    maxRow = data.shape[0]
                    if maxRow > 15:
                        data = data.tail(15)
                        maxRow = 15
                    data_row_list = data.index.tolist()
                    source_sheet.range('H' + str(index + 2)).value = sumSpend
                    png_insert_row_str = 'A' + str(png_insert_row)
                    screenShot.dealExcel(row['帐户编号'],sheetName,maxRow,dataSheet,data_row_list,sumSpend,gg_full_end_row,tmpSheet,png_sheet,png_insert_row_str)
                    png_insert_row += maxRow
                    png_insert_row += 3
        # 保存数据
        source_wb.save()
        source_wb.close()
        data_wb.close()
        app.quit()